package edu.example.house.dao;

import edu.example.bean.buildings;
import edu.example.house.serlvet.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class housemanageDao {
    DataSource dataSource = C3P0Utils.getDataSource();
    QueryRunner queryRunner = new QueryRunner(dataSource);
    public List<buildings> getList(){
        String sql = "select * from buildings";
        BeanListHandler beanListHandler = new BeanListHandler(buildings.class);
        List<buildings> list=null;
        try {
            list = (ArrayList< buildings>) queryRunner.query(sql,beanListHandler);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
    public List<buildings> findById(int id){
        String sql = "select * from buildings WHERE BuildingID = ?";
        BeanListHandler beanListHandler = new BeanListHandler(buildings.class);
        List<buildings> list=null;
        try {
            list = (ArrayList< buildings>) queryRunner.query(sql,beanListHandler,id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
    public Integer buildingAdd(String buildingName,String address,int totalrooms){
        String sql = "insert into buildings(BuildingName,Address,TotalRooms) values(?,?,?)";
        Integer affectrows=null;
        try {
            affectrows=queryRunner.update(sql,buildingName,address,totalrooms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return affectrows;
    }
    public Integer update(int id, String buildingName, String address, int totalRooms) {
        String checkSql = "SELECT COUNT(*) FROM buildings WHERE BuildingName = ?";
        Integer count = null;
        try {
            count = queryRunner.query(checkSql, new ScalarHandler<>(), buildingName);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (count > 1) {
            return -1;
        }
        String sql = "UPDATE buildings SET BuildingName = ?, Address = ?, TotalRooms = ? WHERE BuildingID = ?";
        Integer affectedRows = null;
        try {
            affectedRows = queryRunner.update(sql, buildingName, address, totalRooms, id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return affectedRows;
    }
    public Integer delete(Integer id) {
        String sql = "DELETE FROM buildings WHERE BuildingID = ?";
        Integer affectrows=null;
        try {
         affectrows = queryRunner.update(sql,id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return affectrows;
    }
    public List<buildings> search(String field, String value) {
        String sql = "SELECT * FROM buildings WHERE " + field + " LIKE ?";
        BeanListHandler<buildings> beanListHandler = new BeanListHandler<>(buildings.class);
        List<buildings> list = null;
        try {
            list = queryRunner.query(sql, beanListHandler, "%" + value + "%");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

}
